<?php
include ("includes/rss_php.php");
include ("includes/myconnect.php");
include ("includes/config.php");
$rss = new rss_php;

//clean up database when update
$sqlCleanString = "DELETE FROM tblcurrencyexchangerate2";
mysql_query($sqlCleanString) or die(mysql_error());

// get the rates between USD and the five specific codes from themoneyconverter.com
$tmc_usd_rates = array();
$curLink = $rss_connector;
if($rss->load($curLink, $error) == false){
	echo $error;
	$rss->send_mail($sender_name, $sender_email, $recipient_email, $subject, $error);
	exit;
};
$items = $rss->getItems();
if($items == NULL || $items == false){
	echo $error;
	$rss->send_mail($sender_name, $sender_email, $recipient_email, $subject, 'Failed to parse the ' . $curLink . ' feed. There is no item elements');
	exit;
}
foreach ($items as $item)
{
	$temp=explode("= ", $item['description']);
	$temp1=explode(" ", $temp[1]);
	$temp2=explode("/", $item['title']);
	$currencyRootId = $temp2[1];
	$currencyReferId = $temp2[0];
	$exchangeRate = $temp1[0];
	$tmc_usd_rates[$currencyReferId] = $exchangeRate;
}

// get the rates of the five specific codes from themoneyconverter
/**
 * array('MAD' => array('ARS'=>rate1, 'USD'=>rate2...)
 */
$tmc_rates = array();
$countLinks = count($rss_links1);
for ($i = 0; $i < $countLinks; $i++)
{
	$curLink = $rss_links1[$i];
	echo 'Processing: ' . $curLink . "<br />";
	if($rss->load($curLink, $error) == false){
		echo $error;
		$rss->send_mail($sender_name, $sender_email, $recipient_email, $subject, $error);
		exit;
	};
	$items = $rss->getItems();
	if($items == NULL || $items == false){
		echo $error;
		$rss->send_mail($sender_name, $sender_email, $recipient_email, $subject, 'Failed to parse the ' . $curLink . ' feed. There is no item elements');
		exit;
	}
	foreach ($items as $item)
	{
		$temp=explode("= ", $item['description']);
		$temp1=explode(" ", $temp[1]);
		$temp2=explode("/", $item['title']);
		$currencyRootId = $temp2[1];
		$currencyReferId = $temp2[0];
		$exchangeRate = $temp1[0];
		$tmc_rates[$currencyRootId][$currencyReferId] = $exchangeRate;
	}
}
//-----------------------------

$countLinks = count($rss_links);
$count = 0;

for ($i = 0; $i < $countLinks; $i++)
{
	$error = '';
	$sql_query = "INSERT INTO tblcurrencyexchangerate2(currencyRootId,currencyReferId,exchangeRate) VALUES ";
	$curLink = $rss_links[$i];
	echo 'Processing: ' . $curLink . "<br />";
	if($rss->load($curLink, $error) == false){ // failed to load the rss, send an alert email
		echo $error;
		$rss->send_mail($sender_name, $sender_email, $recipient_email, $subject, $error);
		exit;
	};
	$items = $rss->getItems();
	if($items == NULL || $items == false){ // failed to parse the rss, send an alert email
		$error = 'Failed to parse the ' . $curLink . ' feed. There is no item element';
		echo $error;
		$rss->send_mail($sender_name, $sender_email, $recipient_email, $subject, $error);
		exit;
	}
	$strPattern = '/([A-z]{3}) *= *([A-z]{3}) *\(([\d]*,*[\d]+\.[\d]+)\)/';
	$arrOut = array();
	$currencyRootId = '';
	$sql_query_tmp = array();
	$rate_tmp = array();
	foreach ($items as $item)
	{
		preg_match($strPattern, $item['title'], $arrOut);
		if (is_array($arrOut))
		{
			$currencyRootId = $arrOut[1];
			$currencyReferId = $arrOut[2];
			if($currencyReferId == $currencyRootId) continue;
			$exchangeRate = $arrOut[3];
			$sql_query .= " ('" . $currencyRootId . "','" . $currencyReferId . "','" . $exchangeRate .
                "'),";

			// refer code is USD, caculate rates for 5 of the above currencies, save them to a temporary array
			if($currencyReferId == 'USD' && $currencyRootId != 'USD'){
				foreach($specific_currencies as $specific_currency){
					$sql_query_tmp[$specific_currency] = " ('" . $currencyRootId . "','" . $specific_currency . "','" . ($exchangeRate * $tmc_usd_rates[$specific_currency]) .
                "'),";
					$rate_tmp[$specific_currency][$currencyRootId] = 1/($exchangeRate * $tmc_usd_rates[$specific_currency]);
					$count++;
				}
			}
		}
		$count++;
	}
	if($currencyRootId == 'USD'){
		// add 5 rates, each of the specific money codes
		foreach($specific_currencies as $specific_currency){
			$sql_query .= " ('" . $currencyRootId . "','" . $specific_currency . "','" . $tmc_usd_rates[$specific_currency] .
                "'),";
			$count++;
		}
	}else{
		// build query for updating database for each of the currency
		foreach($specific_currencies as $specific_currency){
			if(isset($tmc_rates[$specific_currency][$currencyRootId])){
				$sql_query .= " ('" . $currencyRootId . "','" . $specific_currency . "','" . (1/$tmc_rates[$specific_currency][$currencyRootId]) .
	                "'),";
			}else{
				$sql_query .= $sql_query_tmp[$specific_currency];
				$tmc_rates[$specific_currency][$currencyRootId] = $rate_tmp[$specific_currency][$currencyRootId];
			}
		}
	}
	// rate against itself
	$sql_query .= " ('" . $currencyRootId . "','" . $currencyRootId . "','" . (1.000000) .
	                "'),";
	$count++;
	$sql_query = substr($sql_query, 0, strlen($sql_query) - 1);
	mysql_query($sql_query) or die(mysql_error());
}

// update specific currency exchange rates
foreach($specific_currencies as $specific_currency){
	$sql_query = "INSERT INTO tblcurrencyexchangerate2(currencyRootId,currencyReferId,exchangeRate) VALUES ";
	foreach($tmc_rates[$specific_currency] as  $referId => $exchangeRate){
		$sql_query .= " ('" . $specific_currency . "','" . $referId . "','" . $exchangeRate .
                "'),";
		$count ++;
	}
	$sql_query .= " ('" . $specific_currency . "','" . $specific_currency . "','" . (1.000000) .
	                "'),";
	$count++;
	$sql_query = substr($sql_query, 0, strlen($sql_query) - 1);
	//echo $sql_query;
	mysql_query($sql_query) or die(mysql_error());
}

//clean and copy to tblcurrencyexchangerate
$sqlCleanString = "DELETE FROM tblcurrencyexchangerate";
mysql_query($sqlCleanString) or die(mysql_error());
$sql = 'SELECT * from tblcurrencyexchangerate2';
$result = mysql_query($sql) or die(mysql_error());
$sql_query = "insert tblcurrencyexchangerate(id,currencyRootId,currencyReferId, exchangeRate) VALUES ";
while ($row = mysql_fetch_assoc($result)) {
	if($row['currencyRootId'] == $row['currencyReferId']){
		// preserve the rate against itself
		$sql_query .= " ('" . $row['id'] . "','" . $row['currencyRootId'] . "','" . $row['currencyRootId'] . "','" . (1.000000) .
	                "'),";
	}else{
		$sql_query .= " ('" . $row['id'] . "','" . $row['currencyRootId'] . "','" . $row['currencyReferId'] . "','" . ($row['exchangeRate']*(($change_rate/100)+1)) ."'),";
	}
}

$sql_query = substr($sql_query, 0, strlen($sql_query) - 1);
mysql_query($sql_query) or die(mysql_error());

echo "Successfully update $count records";

?>
